<?php
require_once('../config/config.inc.php');
require_once('class.baseobject.php');
require_once('class.shift.php');
require_once('class.report.php');
require_once('reportfns.php');

$title = 'Monthly Sales Summary';
$thismonth = isset($_POST['thismonth']) ? $_POST['thismonth'] : date('m');
$thisyear = isset($_POST['thisyear']) ? $_POST['thisyear'] : date('Y');

if($_POST["submit"]=="export to excel") {
	$table="<h3 style='font-size:12px;font-family:arial,helvetica'>$title - $thismonth/$thisyear</h3>";
	$table.="<table border='0' cellspacing='2' cellpadding='2' style='font-size:11px;font-family:arial,helvetica'>";
	$table.=getHeaders();
	$table.=getData($thisyear,$thismonth);
	$table.="</table>";
	$excel_file_name="mss_$thisyear_$thismonth.xls";
	header("Content-type: application/octet-stream");//A MIME attachment with the content type "application/octet-stream" is a binary file.
	header("Content-Disposition: attachment; filename=$excel_file_name");//with this extension of file name you tell what kind of file it is.
	header("Pragma: no-cache");//Prevent Caching
	header("Expires: 0");//Expires and 0 mean that the browser will not cache the page on your hard drive
	echo $table;
	exit;
}


//functions
function getHeaders() {
	$retval="<tr>
<th rowspan=2 colspan=2>Day</th>
<th colspan=10>Guests</th>
<th colspan=4>Room</th>
<!--th rowspan=2 class='grand'>Room<br />Sales</th-->
<th colspan=4>Food</th>
<th colspan=4>Beer</th>
<th rowspan=2 class='grand'>Kitchen<br />Sales</th>

<th colspan=4>Misc</th>

<th colspan=5 class='grand'>Grand Totals</th>

<th rowspan=2 class='grand'>AVG</th>
<th rowspan=2 class='grand'>CUM</th>
</tr>
<tr>
<th>WP</th>
<th>S1</th>
<th>S2</th>
<th>Total</th>
<th>Daily TOR</th>
<th>T CUM TOR</th>
<th>TOR WU</th>
<th>TOR HS1 & HS2</th>
<th>CUM TOR WU</th>
<th>CUM TOR HS1 & HS2</th>
<th>WP</th>
<th>S1</th>
<th>S2</th>
<th>Total</th>

<th>WP</th>
<th>S1</th>
<th>S2</th>
<th>Total</th>

<th>WP</th>
<th>S1</th>
<th>S2</th>
<th>Total</th>

<th>WP</th>
<th>S1</th>
<th>S2</th>
<th>Total</th>

<th>WP</th>
<th>S1</th>
<th>S2</th>
<th>Banquet</th>
<th>GT</th>

</tr>";
return $retval;
}

function getData($thisyear,$thismonth) {
	$cumulative = '';
	$avg = '';
	for($x=1; $x < 32; $x++) {
		if(strlen($x)==1) $x = '0'.$x;
		$date = $thisyear.'-'.$thismonth.'-'.$x;
		$day = date('D',strtotime($date));
		$class='';
		if($day=='Sun' or $day=='Sat') $class='class="weekend"';
		$rows .= "<tr $class>";
		$rows .= "<td>$x</td>";
		$rows .= "<td>" . $day ."</td>";
		
		//guests
		$wp = getSales($date,'guests',1,1);
		$totals['wp'] += $wp;
		$hs1 = getSales($date,'guests',2,1);
		$totals['hs1'] += $hs1;
		$hs2 = getSales($date,'guests',2,2);
		$totals['hs2'] += $hs2;
		$totalguests = getSales($date,'guests');
		$totals['totalguests'] += $totalguests;
		
		$rows .= "<td>" . $wp . "</td>";
		$rows .= "<td>" . $hs1 . "</td>";
		$rows .= "<td>" . $hs2 . "</td>";
		$rows .= "<td>" . $totalguests . "</td>";
		
		//Daily TOR = $totalguests/178;
		if($totalguests!='') {
			$daily_tor = $totalguests/178;
			$daily_tor = ($daily_tor) ? number_format($daily_tor, 2) : '';
		} else {
			$daily_tor = '';
		}
		
		//T CUM TOR = SUM(N^Total)/163/N where N=number of days
		if($totalguests!='') {
			$t_cum_tor = $totals['totalguests']/178/$x;
			//$t_cum_tor = $totalguests/178/$x;
			$t_cum_tor = ($t_cum_tor) ? number_format($t_cum_tor, 2) : '';
		} else {
			$t_cum_tor = '';
		}
		
		//TOR WU = WU/69
		$tor_wu = $wp/69;
		$tor_wu = ($tor_wu) ? number_format($tor_wu,2) : '';
		
		
		//TOR HS1 & HS2 = SUM(HS1 + HS2)/109
		$tor_hs1_hs2 = ($hs1+$hs2)/109;
		$tor_hs1_hs2 = ($tor_hs1_hs2) ? number_format($tor_hs1_hs2, 2) : '';
		
		//CUM TOR WU = SUM(N^WU)/69/N where N=number of days
		if($tor_wu!='') {
			$cum_tor_wu = $totals['wp']/69/$x;
			$cum_tor_wu = ($cum_tor_wu) ? number_format($cum_tor_wu, 2) : '';
		} else {
			$cum_tor_wu = '';
		}
		
		//CUM TOR HS1 & HS2 = SUM(N^HS1 + N^HS2)/109/N
		if($tor_hs1_hs2=='') {
			$cum_tor_hs1_hs2 = '';
		} else {
			$cum_tor_hs1_hs2 = ($totals['hs1'] + $totals['hs2'])/109/$x;
			$cum_tor_hs1_hs2 = ($cum_tor_hs1_hs2) ? number_format($cum_tor_hs1_hs2, 2) : '';
		}
		
		$rows .= "<td>$daily_tor</td>";
		$rows .= "<td>$t_cum_tor</td>";
		$rows .= "<td>$tor_wu</td>";
		$rows .= "<td>$tor_hs1_hs2</td>";
		$rows .= "<td>$cum_tor_wu</td>";
		$rows .= "<td>$cum_tor_hs1_hs2</td>";
		//rooms + ot
		
		$rooms_wp = getSales($date,'roomsales + overtime',1,1);
		$totals['rooms_wp'] += $rooms_wp;
		$rooms_hs1 = getSales($date,'roomsales + overtime',2,1);
		$totals['rooms_hs1'] += $rooms_hs1;
		$rooms_hs2 = getSales($date,'roomsales + overtime',2,2);
		$totals['rooms_hs2'] += $rooms_hs2;
		$rooms_total = getSales($date,'roomsales + overtime');
		$totals['rooms_total'] += $rooms_total;
		
		$rows .= "<td>" .$rooms_wp. "</td>";
		$rows .= "<td>" .$rooms_hs1. "</td>";
		$rows .= "<td>" .$rooms_hs2. "</td>";
		$rows .= "<td>" .$rooms_total. "</td>";
		
		//food
		$food_wp = getSales($date,'food',1,1);
		$totals['food_wp'] += $food_wp;
		$food_hs1 = getSales($date,'food',2,1);
		$totals['food_hs1'] += $food_hs1;
		$food_hs2 = getSales($date,'food',2,2);
		$totals['food_hs2'] += $food_hs2;
		$food_total = getSales($date,'food');
		$totals['food_total'] += $food_total;
		
		$rows .= "<td>" .$food_wp. "</td>";
		$rows .= "<td>" .$food_hs1. "</td>";
		$rows .= "<td>" .$food_hs2. "</td>";
		$rows .= "<td>" .$food_total. "</td>";
		
		
		//beer
		$beer_wp = getSales($date,'beer',1,1);
		$totals['beer_wp'] += $beer_wp;
		$beer_hs1 = getSales($date,'beer',2,1);
		$totals['beer_hs1'] += $beer_hs1;
		$beer_hs2 = getSales($date,'beer',2,2);
		$totals['beer_hs2'] += $beer_hs2;
		$beer_total = getSales($date,'beer');
		$totals['beer_total'] += $beer_total;
		
		$rows .= "<td>" .$beer_wp. "</td>";
		$rows .= "<td>" .$beer_hs1. "</td>";
		$rows .= "<td>" .$beer_hs2. "</td>";
		$rows .= "<td>" .$beer_total. "</td>";
		
		$kitchen = getTotalSales($date,' food + beer + banquet') + $banquet;
		$totals['kitchen']+=$kitchen;
		$rows .= "<th class='grand'>" .$kitchen. "</td>";
		
		
		$misc_wp = getSales($date,'misc',1,1);
		$totals['misc_wp'] += $misc_wp;
		$misc_hs1 = getSales($date,'misc',2,1);
		$totals['misc_hs1'] += $misc_hs1;
		$misc_hs2 = getSales($date,'misc',2,2);
		$totals['misc_hs2'] += $misc_hs2;
		$misc_total = getSales($date,'misc');
		$totals['misc_total'] += $misc_total;
		
		$rows .= "<td>" .getSales($date,'misc',1,1). "</td>";
		$rows .= "<td>" .getSales($date,'misc',2,1). "</td>";
		$rows .= "<td>" .getSales($date,'misc',2,2). "</td>";
		$rows .= "<td>" .getSales($date,'misc'). "</td>";
		
		//rooms
		$grand_wp = getTotalSales($date,'roomsales + overtime + food + beer + misc',1,1);
		$totals['grand_wp'] += $grand_wp;
		$grand_hs1 = getTotalSales($date,'roomsales + overtime + food + beer + misc',2,1);
		$totals['grand_hs1'] += $grand_hs1;
		$grand_hs2 = getTotalSales($date,'roomsales + overtime + food + beer + misc',2,2);
		$totals['grand_hs2'] += $grand_hs2;
		
		
		$rows .= "<td>" . $grand_wp . "</td>";
		$rows .= "<td>" . $grand_hs1 . "</td>";
		$rows .= "<td>" .$grand_hs2. "</td>";
		$banquet = getBanquetPerDay($thisyear,$thismonth,$x);
		$totals['banquet']+=$banquet;
		$rows .= "<td>" .$banquet. "</td>";
		$total = getTotalSales($date,'roomsales + overtime + food + beer + misc') + $banquet;
		$totals['grand_total']+=$total;
		$rows .= "<th class='grand'>" .number_format($total,0) . "</th>";
		
		
		
		if($total) {
			$prev = $avg; 
			$cumulative +=$total;
			$avg = ceil($cumulative/$x );
			$rows .= "<th class='grand'>" .number_format($avg,0). "</td>";
			$rows .= "<th class='grand'>" .number_format($cumulative,0). "</td>";
			
		}else{
			$rows .= "<td></td>";
			$rows .= "<td></td>";
		}
		
		$rows .= '</tr>';
	}
	$rows .= getFooter($totals);
	return $rows;
}

function getFooter($totals) {
	$ftr = "<tr><th colspan='2'>Totals</th>";
	$ftr.= "<th>". $totals['wp']."</th>";
	$ftr.= "<th>". $totals['hs1']."</th>";
	$ftr.= "<th>". $totals['hs2']."</th>";
	$ftr.= "<th>". $totals['totalguests']."</th>";
	for($x=0; $x < 6; $x++) {
		$ftr .= "<th>&nbsp;</th>";
	}
	$ftr.= "<th>". $totals['rooms_wp']."</th>";
	$ftr.= "<th>". $totals['rooms_hs1']."</th>";
	$ftr.= "<th>". $totals['rooms_hs2']."</th>";
	$ftr.= "<th>". $totals['rooms_total']."</th>";
	
	$ftr.= "<th>". $totals['food_wp']."</th>";
	$ftr.= "<th>". $totals['food_hs1']."</th>";
	$ftr.= "<th>". $totals['food_hs2']."</th>";
	$ftr.= "<th>". $totals['food_total']."</th>";
	
	$ftr.= "<th>". $totals['beer_wp']."</th>";
	$ftr.= "<th>". $totals['beer_hs1']."</th>";
	$ftr.= "<th>". $totals['beer_hs2']."</th>";
	$ftr.= "<th>". $totals['beer_total']."</th>";
	
	$ftr.= "<th>". $totals['kitchen']."</th>";
	$ftr.= "<th>". $totals['misc_wp']."</th>";
	$ftr.= "<th>". $totals['misc_hs1']."</th>";
	$ftr.= "<th>". $totals['misc_hs2']."</th>";
	$ftr.= "<th>". $totals['misc_total']."</th>";
	
	$ftr.= "<th>". $totals['grand_wp']."</th>";
	$ftr.= "<th>". $totals['grand_hs1']."</th>";
	$ftr.= "<th>". $totals['grand_hs2']."</th>";
	
	$ftr.= "<th>". $totals['banquet']."</th>";
	$ftr.= "<th>". $totals['grand_total']."</th>";
	$ftr.= "<th></th>";
	$ftr.="</tr>";
	return $ftr;
}

function getMonth($selected) {
	$ret = "<select name='thismonth' id='thismonth'>";
	for($x=1; $x <=12; $x++) {
		$month = date('F', strtotime('2010-' . $x . '-01'));
		$ret.="<option value='$x' ";
		if($selected==$x) $ret.=" selected ";
		$ret.=">$month</option>";
	}
	$ret.="</select>";
	return $ret;
}

function getYear($selected) {
	$ret = "<select name='thisyear' id='thisyear'>";
	for($x=2010; $x <=2015; $x++) {
		$ret.="<option value='$x' ";
		if($selected==$x) $ret.=" selected ";
		$ret.=">$x</option>";
	}
	$ret.="</select>";
	return $ret;
}

function getSales($date,$category,$type=null,$site=null) {
	$sql  = " select sum($category) from salessummary 
			  where salesdate='$date'  ";
	if(!is_null($type) && !is_null($site)) $sql.=" and type='$type' and site='$site' ";
	$res = mysql_query($sql) or die($sql);
	$row = mysql_fetch_row($res);
	return $row[0];
}

function getBanquetPerDay($yr,$mo,$dy) {
	$sql = "select (unit_cost*qty) as totalcost from fnb_sales 
	where item_id=115 and year(update_date)=$yr and month(update_date)=$mo and day(update_date)=$dy";
	$res = mysql_query($sql);
	if(mysql_num_rows($res)) {
		list($retval)=mysql_fetch_row($res);
	}else{
		$retval=0;
	}
	return $retval;
}

function getTotalSales($date,$fields,$type=null,$site=null) {
	$sql  = " select sum($fields) from salessummary 
			  where salesdate='$date'  ";
	if(!is_null($type) && !is_null($site)) $sql.=" and type='$type' and site='$site' ";
	$res = mysql_query($sql) or die($sql);
	$row = mysql_fetch_row($res);
	return $row[0];
}
?>
<html>
<head>
<link rel="stylesheet" type="text/css" href="./reports.css">
<link rel="stylesheet" type="text/css" href="../../css/start/jquery-ui.css">
<script type="text/javascript" src="../../js/jquery.js"></script>
<script type="text/javascript" src="../../js/jquery-ui.js"></script>
<style>
table {
	border-collapse:collapse;
}
table th,td {
	padding:4px;
	text-align:center;
}

th.grand, td.grand {
	background-color:#eeffcc;
}

tr.weekend {
	background-color:#ffeecc;
}
</style>
</head>
<body>
<form method='post'>
<div>
Select Month: <?php echo getmonth($thismonth) . getyear($thisyear) ?>
<input type="submit" name="submit" value="go" />
<input type="submit" name="submit" value="export to excel" />
</div>
<div id="workpanel">
<table border='1'>
<?php echo getHeaders() ?>
<?php

echo getData($thisyear,$thismonth);
?>
</table>
</div>
</form>
<script>
$(document).ready(function(){
	$("#newdate").datepicker({dateFormat:'yy-mm-dd'});
});
</script>
</body>
</html>

